{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# `NDSparse` Usage\n",
    "\n",
    "The purpose of this section is to show you how to use the methods associated with NDSparse objects. If you've already worked through the section on Table Usage, this notebook will seem very familiar!\n",
    "\n",
    "Let's get started by loading JuliaDB and editing our default settings so that tables will display their entries."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "using JuliaDB\n",
    "\n",
    "IndexedTables.set_show_compact!(false);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- `loadndsparse` is an analog to `loadtable`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2-d NDSparse with 56023 values (6 field named tuples):\n",
       "Ticker         Date       │ Open     High     Low      Close    Volume    OpenInt\n",
       "──────────────────────────┼──────────────────────────────────────────────────────\n",
       "\"aapl.us.txt\"  1984-09-07 │ 0.42388  0.42902  0.41874  0.42388  23220030  0\n",
       "\"aapl.us.txt\"  1984-09-10 │ 0.42388  0.42516  0.41366  0.42134  18022532  0\n",
       "\"aapl.us.txt\"  1984-09-11 │ 0.42516  0.43668  0.42516  0.42902  42498199  0\n",
       "\"aapl.us.txt\"  1984-09-12 │ 0.42902  0.43157  0.41618  0.41618  37125801  0\n",
       "\"aapl.us.txt\"  1984-09-13 │ 0.43927  0.44052  0.43927  0.43927  57822062  0\n",
       "\"aapl.us.txt\"  1984-09-14 │ 0.44052  0.45589  0.44052  0.44566  68847968  0\n",
       "\"aapl.us.txt\"  1984-09-17 │ 0.45718  0.46357  0.45718  0.45718  53755262  0\n",
       "\"aapl.us.txt\"  1984-09-18 │ 0.45718  0.46103  0.44052  0.44052  27136886  0\n",
       "\"aapl.us.txt\"  1984-09-19 │ 0.44052  0.44566  0.43157  0.43157  29641922  0\n",
       "\"aapl.us.txt\"  1984-09-20 │ 0.43286  0.43668  0.43286  0.43286  18453585  0\n",
       "\"aapl.us.txt\"  1984-09-21 │ 0.43286  0.44566  0.42388  0.42902  27842780  0\n",
       "\"aapl.us.txt\"  1984-09-24 │ 0.42902  0.43157  0.42516  0.42516  22033109  0\n",
       "                          ⋮\n",
       "\"tsla.us.txt\"  2017-10-27 │ 319.75   324.59   316.66   320.87   6970118   0\n",
       "\"tsla.us.txt\"  2017-10-30 │ 319.18   323.78   317.25   320.08   4254378   0\n",
       "\"tsla.us.txt\"  2017-10-31 │ 320.23   331.95   320.18   331.53   5672347   0\n",
       "\"tsla.us.txt\"  2017-11-01 │ 332.25   332.609  320.26   321.08   8457336   0\n",
       "\"tsla.us.txt\"  2017-11-02 │ 300.13   308.69   292.63   299.26   19791416  0\n",
       "\"tsla.us.txt\"  2017-11-03 │ 299.5    306.25   295.13   306.09   8893974   0\n",
       "\"tsla.us.txt\"  2017-11-06 │ 307.0    307.5    299.01   302.78   6482486   0\n",
       "\"tsla.us.txt\"  2017-11-07 │ 301.02   306.5    300.03   306.05   5286320   0\n",
       "\"tsla.us.txt\"  2017-11-08 │ 305.5    306.89   301.3    304.31   4725510   0\n",
       "\"tsla.us.txt\"  2017-11-09 │ 302.5    304.46   296.3    302.99   5440335   0\n",
       "\"tsla.us.txt\"  2017-11-10 │ 302.5    308.36   301.85   302.99   4621912   0"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nd = loadndsparse(\"stocksample\", filenamecol = :Ticker, indexcols = [:Ticker, :Date])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Having made `:Ticker` and `:Date` the columns by which we index entries to `nd`, we can perform a lookup in `nd` via"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(Open = 0.38289, High = 0.39186, Low = 0.37906, Close = 0.38164, Volume = 31191161, OpenInt = 0)"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nd[\"aapl.us.txt\", Date(1986, 2, 10)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# `selectkeys` and `selectvalues`\n",
    "\n",
    "- Rather than `select`, for `NDSparse` you can use `selectkeys` and `selectvalues`.\n",
    "\n",
    "- In this example, we generate a NDSparse object that contains two indexable columns (`:Ticker` and `:Date`) and one column of selected values, `:Close`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2-d NDSparse with 56023 values (Float64):\n",
       "Ticker         Date       │\n",
       "──────────────────────────┼────────\n",
       "\"aapl.us.txt\"  1984-09-07 │ 0.42388\n",
       "\"aapl.us.txt\"  1984-09-10 │ 0.42134\n",
       "\"aapl.us.txt\"  1984-09-11 │ 0.42902\n",
       "\"aapl.us.txt\"  1984-09-12 │ 0.41618\n",
       "\"aapl.us.txt\"  1984-09-13 │ 0.43927\n",
       "\"aapl.us.txt\"  1984-09-14 │ 0.44566\n",
       "\"aapl.us.txt\"  1984-09-17 │ 0.45718\n",
       "\"aapl.us.txt\"  1984-09-18 │ 0.44052\n",
       "\"aapl.us.txt\"  1984-09-19 │ 0.43157\n",
       "\"aapl.us.txt\"  1984-09-20 │ 0.43286\n",
       "\"aapl.us.txt\"  1984-09-21 │ 0.42902\n",
       "\"aapl.us.txt\"  1984-09-24 │ 0.42516\n",
       "                          ⋮\n",
       "\"tsla.us.txt\"  2017-10-27 │ 320.87\n",
       "\"tsla.us.txt\"  2017-10-30 │ 320.08\n",
       "\"tsla.us.txt\"  2017-10-31 │ 331.53\n",
       "\"tsla.us.txt\"  2017-11-01 │ 321.08\n",
       "\"tsla.us.txt\"  2017-11-02 │ 299.26\n",
       "\"tsla.us.txt\"  2017-11-03 │ 306.09\n",
       "\"tsla.us.txt\"  2017-11-06 │ 302.78\n",
       "\"tsla.us.txt\"  2017-11-07 │ 306.05\n",
       "\"tsla.us.txt\"  2017-11-08 │ 304.31\n",
       "\"tsla.us.txt\"  2017-11-09 │ 302.99\n",
       "\"tsla.us.txt\"  2017-11-10 │ 302.99"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "selectvalues(nd, :Close)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# `map`\n",
    "\n",
    "- The syntax for `map` is the same for `Table` and `NDSparse` objects:\n",
    "\n",
    "`map(function, table; select)`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2-d NDSparse with 56023 values (Float64):\n",
       "Ticker         Date       │\n",
       "──────────────────────────┼────────\n",
       "\"aapl.us.txt\"  1984-09-07 │ 0.01028\n",
       "\"aapl.us.txt\"  1984-09-10 │ 0.0115\n",
       "\"aapl.us.txt\"  1984-09-11 │ 0.01152\n",
       "\"aapl.us.txt\"  1984-09-12 │ 0.01539\n",
       "\"aapl.us.txt\"  1984-09-13 │ 0.00125\n",
       "\"aapl.us.txt\"  1984-09-14 │ 0.01537\n",
       "\"aapl.us.txt\"  1984-09-17 │ 0.00639\n",
       "\"aapl.us.txt\"  1984-09-18 │ 0.02051\n",
       "\"aapl.us.txt\"  1984-09-19 │ 0.01409\n",
       "\"aapl.us.txt\"  1984-09-20 │ 0.00382\n",
       "\"aapl.us.txt\"  1984-09-21 │ 0.02178\n",
       "\"aapl.us.txt\"  1984-09-24 │ 0.00641\n",
       "                          ⋮\n",
       "\"tsla.us.txt\"  2017-10-27 │ 7.93\n",
       "\"tsla.us.txt\"  2017-10-30 │ 6.53\n",
       "\"tsla.us.txt\"  2017-10-31 │ 11.77\n",
       "\"tsla.us.txt\"  2017-11-01 │ 12.349\n",
       "\"tsla.us.txt\"  2017-11-02 │ 16.06\n",
       "\"tsla.us.txt\"  2017-11-03 │ 11.12\n",
       "\"tsla.us.txt\"  2017-11-06 │ 8.49\n",
       "\"tsla.us.txt\"  2017-11-07 │ 6.47\n",
       "\"tsla.us.txt\"  2017-11-08 │ 5.59\n",
       "\"tsla.us.txt\"  2017-11-09 │ 8.16\n",
       "\"tsla.us.txt\"  2017-11-10 │ 6.51"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "map(r -> r.High - r.Low, nd; select = (:High, :Low))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# `filter`\n",
    "\n",
    "- The syntax for filter is the same for `Table` and `NDSparse` objects:\n",
    "\n",
    "`filter(function, tablename; select)`\n",
    "\n",
    "- In the following example, we filter `nd` to create a new `NDSparse` object that only contains days/stocks that increased in value:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2-d NDSparse with 26302 values (6 field named tuples):\n",
       "Ticker         Date       │ Open     High     Low      Close    Volume    OpenInt\n",
       "──────────────────────────┼──────────────────────────────────────────────────────\n",
       "\"aapl.us.txt\"  1984-09-11 │ 0.42516  0.43668  0.42516  0.42902  42498199  0\n",
       "\"aapl.us.txt\"  1984-09-14 │ 0.44052  0.45589  0.44052  0.44566  68847968  0\n",
       "\"aapl.us.txt\"  1984-11-23 │ 0.37394  0.38419  0.37394  0.37906  38231510  0\n",
       "\"aapl.us.txt\"  1984-12-14 │ 0.41111  0.42516  0.41111  0.42134  26812043  0\n",
       "\"aapl.us.txt\"  1984-12-28 │ 0.44309  0.46103  0.44052  0.45845  46108958  0\n",
       "\"aapl.us.txt\"  1985-01-18 │ 0.44821  0.46742  0.44694  0.45718  98352467  0\n",
       "\"aapl.us.txt\"  1985-01-25 │ 0.46357  0.47251  0.45335  0.47251  88813324  0\n",
       "\"aapl.us.txt\"  1985-02-15 │ 0.44052  0.44821  0.43668  0.44694  48420341  0\n",
       "\"aapl.us.txt\"  1985-02-22 │ 0.42902  0.44566  0.42902  0.44052  63175710  0\n",
       "\"aapl.us.txt\"  1985-03-01 │ 0.39443  0.39699  0.38289  0.39699  69004139  0\n",
       "\"aapl.us.txt\"  1985-03-15 │ 0.34702  0.36883  0.34448  0.36112  50594286  0\n",
       "\"aapl.us.txt\"  1985-03-29 │ 0.34962  0.35473  0.34962  0.35344  24313240  0\n",
       "                          ⋮\n",
       "\"tsla.us.txt\"  2017-10-11 │ 353.89   357.6    351.15   354.6    4495328   0\n",
       "\"tsla.us.txt\"  2017-10-12 │ 352.95   359.78   352.64   355.68   4075519   0\n",
       "\"tsla.us.txt\"  2017-10-17 │ 350.91   356.22   350.07   355.75   3290317   0\n",
       "\"tsla.us.txt\"  2017-10-18 │ 355.97   363.0    354.13   359.65   4936166   0\n",
       "\"tsla.us.txt\"  2017-10-27 │ 319.75   324.59   316.66   320.87   6970118   0\n",
       "\"tsla.us.txt\"  2017-10-30 │ 319.18   323.78   317.25   320.08   4254378   0\n",
       "\"tsla.us.txt\"  2017-10-31 │ 320.23   331.95   320.18   331.53   5672347   0\n",
       "\"tsla.us.txt\"  2017-11-03 │ 299.5    306.25   295.13   306.09   8893974   0\n",
       "\"tsla.us.txt\"  2017-11-07 │ 301.02   306.5    300.03   306.05   5286320   0\n",
       "\"tsla.us.txt\"  2017-11-09 │ 302.5    304.46   296.3    302.99   5440335   0\n",
       "\"tsla.us.txt\"  2017-11-10 │ 302.5    308.36   301.85   302.99   4621912   0"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filter(r -> r.Open < r.Close, nd)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# `reduce` and `groupreduce`\n",
    "\n",
    "- Syntax is the same as for `Table`:\n",
    "\n",
    "`reduce(reducer, table; select)`\n",
    "\n",
    "`groupreduce(reducer, table, by; select)`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1-d NDSparse with 8 values (Int64):\n",
       "Ticker         │\n",
       "───────────────┼─────────────\n",
       "\"aapl.us.txt\"  │ 891950579821\n",
       "\"amzn.us.txt\"  │ 40385735209\n",
       "\"dis.us.txt\"   │ 85815802336\n",
       "\"googl.us.txt\" │ 26503128932\n",
       "\"ibm.us.txt\"   │ 81302723803\n",
       "\"msft.us.txt\"  │ 634313240042\n",
       "\"nflx.us.txt\"  │ 62518969374\n",
       "\"tsla.us.txt\"  │ 8205871633"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "groupreduce(+, nd, :Ticker; select = :Volume)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# `groupby`\n",
    "\n",
    "- Syntax is the same as for `Table`:\n",
    "\n",
    "`groupby(function, table, by; select)`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1-d NDSparse with 8 values (2-tuples):\n",
       "Ticker         │ 2        3\n",
       "───────────────┼─────────────────\n",
       "\"aapl.us.txt\"  │ 0.23051  175.61\n",
       "\"amzn.us.txt\"  │ 1.4      1132.88\n",
       "\"dis.us.txt\"   │ 0.28298  118.24\n",
       "\"googl.us.txt\" │ 50.005   1058.29\n",
       "\"ibm.us.txt\"   │ 3.3901   186.36\n",
       "\"msft.us.txt\"  │ 0.0672   84.56\n",
       "\"nflx.us.txt\"  │ 1.29     202.68\n",
       "\"tsla.us.txt\"  │ 15.8     385.0"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "groupby(extrema, nd, :Ticker; select = :Close)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# `summarize`\n",
    "\n",
    "- Syntax is the same as for `Table`:\n",
    "\n",
    "`summarize(function[s], table[, by]; select)`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1-d NDSparse with 8 values (4 field named tuples):\n",
       "Ticker         │ Open_mean  Close_mean  Open_std  Close_std\n",
       "───────────────┼───────────────────────────────────────────\n",
       "\"aapl.us.txt\"  │ 22.2844    22.281      37.7634   37.7645\n",
       "\"amzn.us.txt\"  │ 181.747    181.769     239.611   239.548\n",
       "\"dis.us.txt\"   │ 20.6162    20.6212     26.4788   26.4787\n",
       "\"googl.us.txt\" │ 389.993    389.856     235.105   235.102\n",
       "\"ibm.us.txt\"   │ 48.5355    48.5542     49.271    49.2977\n",
       "\"msft.us.txt\"  │ 18.9779    18.9847     16.4161   16.424\n",
       "\"nflx.us.txt\"  │ 39.5034    39.5213     47.5678   47.5733\n",
       "\"tsla.us.txt\"  │ 150.39     150.355     107.072   107.024"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "summarize((mean, std), nd, :Ticker; select = (:Open, :Close))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# `columns` and `rows`\n",
    "\n",
    "- Syntax is the same as for `Table`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(Ticker = String[\"aapl.us.txt\", \"aapl.us.txt\", \"aapl.us.txt\", \"aapl.us.txt\", \"aapl.us.txt\", \"aapl.us.txt\", \"aapl.us.txt\", \"aapl.us.txt\", \"aapl.us.txt\", \"aapl.us.txt\"  …  \"tsla.us.txt\", \"tsla.us.txt\", \"tsla.us.txt\", \"tsla.us.txt\", \"tsla.us.txt\", \"tsla.us.txt\", \"tsla.us.txt\", \"tsla.us.txt\", \"tsla.us.txt\", \"tsla.us.txt\"], Date = Date[1984-09-07, 1984-09-10, 1984-09-11, 1984-09-12, 1984-09-13, 1984-09-14, 1984-09-17, 1984-09-18, 1984-09-19, 1984-09-20  …  2017-10-30, 2017-10-31, 2017-11-01, 2017-11-02, 2017-11-03, 2017-11-06, 2017-11-07, 2017-11-08, 2017-11-09, 2017-11-10], Open = [0.42388, 0.42388, 0.42516, 0.42902, 0.43927, 0.44052, 0.45718, 0.45718, 0.44052, 0.43286  …  319.18, 320.23, 332.25, 300.13, 299.5, 307.0, 301.02, 305.5, 302.5, 302.5], High = [0.42902, 0.42516, 0.43668, 0.43157, 0.44052, 0.45589, 0.46357, 0.46103, 0.44566, 0.43668  …  323.78, 331.95, 332.609, 308.69, 306.25, 307.5, 306.5, 306.89, 304.46, 308.36], Low = [0.41874, 0.41366, 0.42516, 0.41618, 0.43927, 0.44052, 0.45718, 0.44052, 0.43157, 0.43286  …  317.25, 320.18, 320.26, 292.63, 295.13, 299.01, 300.03, 301.3, 296.3, 301.85], Close = [0.42388, 0.42134, 0.42902, 0.41618, 0.43927, 0.44566, 0.45718, 0.44052, 0.43157, 0.43286  …  320.08, 331.53, 321.08, 299.26, 306.09, 302.78, 306.05, 304.31, 302.99, 302.99], Volume = [23220030, 18022532, 42498199, 37125801, 57822062, 68847968, 53755262, 27136886, 29641922, 18453585  …  4254378, 5672347, 8457336, 19791416, 8893974, 6482486, 5286320, 4725510, 5440335, 4621912], OpenInt = [0, 0, 0, 0, 0, 0, 0, 0, 0, 0  …  0, 0, 0, 0, 0, 0, 0, 0, 0, 0])"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "columns(nd)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Julia 0.6.2-pre",
   "language": "julia",
   "name": "julia-0.6"
  },
  "language_info": {
   "file_extension": ".jl",
   "mimetype": "application/julia",
   "name": "julia",
   "version": "0.6.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
